PostGIS

Configuration

We used to say more about the installation about PostGIS, but the documentation on http://postgis.refractions.netshould be sufficient, and if not the PostGIS users list is the place to go for help. We do highly recommend that you install GEOS before you install PostGIS, as it will speed up spatial queries in GeoServer, we automatically detectits presence and use it if available. If you've installed PostGIS through the PostgreSQL Windows installer then it's automatically included.

If you are a windows users then you might check out Postgres 8, which has an .exe installer for windows. It should offer much, much better windows support, including installers and the ability to run as a windows service. Check the PostgreSQL Homepage for more information. The only way to run Postgres 7 on Windows is to use cygwin, which we highly recommend, but which can be a bitch to set up. PostGIS will work with both 7 and 8. If you are a windows user and must use 7, check out the DCMMS SourceForge project, which has a great PostGIS installer.

If you have just installed PostGIS then we recommend connecting to it with the command line psql tool before testing with GeoServer, just to make sure that it works. When running it be sure to start it using the -i flag,which allows GeoServer (and other TCP/IP connections), should look something like:

postmaster -i -D /your/data/folder/location

Then fill out the PostGIS specific parameters as follows:

Option Name	Description
host	Must match the PostGIS postmaster daemon URI exactly, portexcluded. Can be a number or name - localhost or 127.0.0.1 ifPostGIS is on the same machine as geoserver, if not it canreference the ip address or host name.
port	Must match the PostGIS postmaster daemon port exactly(generally 5432).
database	This is the name of the postgis database to connect to. Each PostGIS instance may run one or more databases, created withthe createdb tool, and then spatially enabled by running thepostgis.sql script. The database param must match the name of thedatabase created.
user	The name of the user to connect to the database. Must haveappropriate privileges on the database connecting to. Note that through this mechanism you can actually get finer control of transactions in GeoServer. You do this by creating a user that does not have transaction privileges on the database, and only letting GeoServer connect with that user. This will not bereflected in the Capabilities documents, but all attemptedinserts, updates, and deletes will be rejected.
passwd	Must match the PostGIS database user passwordexactly.
wkb enabled	Sets whether the GeoTools library should use the faster Well Known Binary (WKB) format to read the PostGIS databse, or the more tested Well Known Text (WKT) format. At this point we believe WKB is well tested enough that we recommend using it, as it is more accurate and faster.
loose bbox	This is an option for speed in bbox requests, with a slight hit in accuracy. It uses a faster bounding box operation for requests to PostGIS, that just compares against the envelope ofthe geometry, instead of the full geometry. This means that a fewcomparisons could be slightly wrong. We recommend setting this to true if you're mostly using GeoServer for the WMS, as a few extra features do not hurt in the production of an image. For the WFSone should note that using a NOT DISJOINT filter will still beexactly accurate, it's only the BBOX filter, which is intended as a shortcut for NOT DISJOINT, that will be slightly off (and much faster).
estimated extent	This option effects how table extents are calculated. If set to true then the postgis function estimated_exent will be used when the entire bounds of a table is being requested. Be warned that this will result in a table bounds that is not guaraneteed to be accurate. In some cases ( depending on how closely the data follows a uniform distribution ), it can be very inaccurate. This function relies on up to date table statistics. So you should run a 'VACUUM ANALYZE' at least once on the table before the function is used, and regularly thereafter depending on how much the data in the table changes. Unless you have very large data sets you likely do not need this option

Note that if you are using files directly, instead of the web admin tool, you will also have to make sure to fill out a 'dbtype' param equal to postgis (the web admin tool does this automatically)

PostGIS View as a FeatureType

Just like using a table, a view in PostGIS can be used as a FeatureType. The need for using views may be for example
a dynamic selection of a subset of features in a table, or transform the geometries to some other projection defined in PostGIS without duplicating data.

To use a PostGIS view as a FeatureType in Geoserver, do the following:

1. Create the view, which must contain the oid field from the source table:

   CREATE OR REPLACE  VIEW test AS SELECT oid, key, country, geom FROM countries;

   Here the countries table contains MULTIPOLYGON geometries in the geom field.

2. After creating the view, insert a record into the geometry_columns table:

   INSERT INTO "geometry_columns" VALUES ('','public','test','geom',2,4326,'MULTIPOLYGON');

   Here test is the name of our view, 2 is the number of dimensions, 4326 is the SRID of the geometries in the geom field,
   and MULTIPOLYGON is the type of geometry.

3. Configure the PostGIS view as a FeatureType in Geoserver:
   Assuming that you already have a PostGIS datastore defined, use Geoserver's web interface
   and go to the "Config/Data/FeatureType/New" page. You should see the view in the "FeatureType" dropdown list and select it.
   Click on the "New" button, which will take you to a page where you can define default Style for the FeatureType,
   change the name and generate the Bounding Box. After all that, click on the "Apply, "Save", and "Load" buttons on the left side of the page.
   Alternatively, the FeatureType can be defined by manually creating a directory ("localPostgis_test" in this example),
   with an info.xml file in the featureTypes folder, which is itself located in Geoserver's data directory. The catalog still
   needs to be reloaded, so the new FeatureType is available.

The data in the PostGIS view is now ready to be used as a FeatureType in Geoserver.

Performance optimization

When dealing with large amount of data optimal disk access is important. You may notice your GeoServer being slow, and see low CPU consumption and high disk usage. This occurs when data access is the bottleneck, and the reason is usually lack of spatial indexes or bad data layout.
In order to improve performance in this specific case:
First, make sure a spatial index is available on your geometry columns. If the disk is still the bottleneck you may try clustering your tables with the following commands:

alter table myTable alter theGeomColumn set not null;
cluster myTableGeometryIndex on myTable;

Clustering will reorganize the data layout so that it follows index order, allowing for faster retrieval. This is especially effective if your data does not change often, since the cluster command will just reorganize your data once. For more information about clustering data in Postgres see the cluster command description on the Postgres manual.

Troubleshooting

NULL extent, error building attribute type.

I got the following error whe I tried to create a feature-type in linux, Using PostGIS as the datasource:
In the web-interface:
>The FeatureType 'positions' has a NULL extent.
>HINT: the dataset is empty or has no default geometry attribute.
In the tomcat logs (logs/catalina.out):
>WARNING org.geotools.data.jdbc.JDBCDataStore - Error building attribute type. The column will be ignored
It turned out that there was an access-denied sql-error from the postgis-server on the table spatial_ref_sys. This error did not appear when I first tested the application on windows. The tomcat-user originally had read-access to the table, so Im not sure why the error appeared. Granting all previliges on this table to the tomcat database user fixed the error. It would be good if the warning from JDBCDataStore included the SQLError.getMessage() returned from the original SQL-error thrown.

"ERROR: column "oid" does not exist."

When trying to access features in a column that has been added, a "ERROR: column "oid" does not exist." appears.

This is due to the table not having a proper primary key. Oftentimes data imported (including with OGR) does not declare a primary key, which GeoServer needs to identify each unique feature id. To fix this you need to declare a primary key:

ALTER TABLE <tablename> ADD CONSTRAINT fid_pkey PRIMARY KEY  (<primary key column>);

If your table already has a column that is unique (such as ogc_fid in OGR tables), then you can just use that as the primary key column. The constraint you can name as you like, we just picked fid_pkey in this example.

If your table does not already have a unique column, you can create one with a call like:

alter table <tablename> add column fid serial;

This will create a column called 'fid' that is filled with unique values. You can then use this fid column as the primary key.

PostGIS View as a FeatureType only worked for me (Geoserver version 1.3.0) when I put an "oid" column in the view, even if the table in the "FROM" clause has a proper primary key. My workaround was to use an alias, something like this: CREATE VIEW AS select pk_column as oid, ..., geom from table;   Once Geoserver "sees" the oid field, it works. Posted by eduardoescovar at Aug 31, 2006 15:33

I have couple of tables representing various layers of a state. Each table ( PostGIS ) represent one of the following Roads City/Area marking Various locality names etc Could anyone help me understand on how to setup a geoserver setup which would show up all the layers in the map? That is, the map should be showing data from all the tables ( roads, names etc ) and correctly overlay ? Any help is greatly appreciated. Posted by rakeshxp at Aug 20, 2007 01:43